using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Configuration;
namespace Customs.Components.KDT.GC
{
    public class PhuKien : PhuKienInfo
    {
        public void Load(PhuKienInfo entity)
        {
            this._dongia = entity.DonGia;
            this._masp = entity.MaNhomSanPham;
            this._MaDoanhNghiep = entity.MaDoanhNghiep;
            this._MaDVT = entity.DVT_ID;
            this._MaHaiQuan = entity.MaHaiQuan;
            this._MaHS = entity.MaHS;
            this._MaPhuKien = entity.MaPhuKien;
            this._NgayKy = entity.NgayKy;
            this._nguyente = entity.NguyenTe_ID;
            this._NuocXX_ID = entity.NuocXX_ID;
            this._P_code = entity.P_Code;
            this._SoHopDong = entity.SoHopDong;
            this._SoLuong = entity.SoLuong;
            this._SoPhuKien = entity.SoPhuKien;
            this._STT = entity.STTHang;
            this._tenSp = entity.Ten;
            this._ThongTinCu = entity.ThongTinCu;
            this._tinhtrang = entity.TinhTrang;
            this._trigia = entity.TriGia;            
        }
        public bool Load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) this.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoPK"))) this._SoPhuKien = reader.GetString(reader.GetOrdinal("SoPK"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaPK"))) this._MaPhuKien = reader.GetString(reader.GetOrdinal("MaPK"));
                if (!reader.IsDBNull(reader.GetOrdinal("PCode"))) this._P_code = reader.GetString(reader.GetOrdinal("PCode"));
                if (!reader.IsDBNull(reader.GetOrdinal("ThongTinCu"))) this._ThongTinCu = reader.GetString(reader.GetOrdinal("ThongTinCu"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten"))) this._tenSp = reader.GetString(reader.GetOrdinal("Ten"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) this._SoLuong = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("DVT_ID"))) this._MaDVT = reader.GetString(reader.GetOrdinal("DVT_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma"))) this._masp = reader.GetString(reader.GetOrdinal("Ma"));
                if (!reader.IsDBNull(reader.GetOrdinal("NuocXX_ID"))) this._NuocXX_ID = reader.GetString(reader.GetOrdinal("NuocXX_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("TinhTrang"))) this._tinhtrang = reader.GetString(reader.GetOrdinal("TinhTrang"));
                if (!reader.IsDBNull(reader.GetOrdinal("DonGia"))) this._dongia = reader.GetDecimal(reader.GetOrdinal("DonGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGia"))) this._trigia = reader.GetDecimal(reader.GetOrdinal("TriGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("NguyenTe_ID"))) this._nguyente = reader.GetString(reader.GetOrdinal("NguyenTe_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) this._MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this._STT = reader.GetInt32(reader.GetOrdinal("STTHang"));
          

                return true;
            }
            return false;
        }
        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("So_PK AS [SoPK], ");
            query.Append("Ma_PK AS [MaPK], ");
            query.Append("P_Code AS [PCode], ");
            query.Append("Old_info AS [ThongTinCu], ");
            query.Append("Ten_SP AS [Ten], ");
            query.Append("So_Luong AS [SoLuong], ");
            query.Append("Ma_DVT AS [DVT_ID], ");
            query.Append("Ma_SP AS [Ma], ");
            query.Append("xuat_xu AS [NuocXX_ID], ");
            query.Append("TinhTrang AS [TinhTrang], ");
            query.Append("DonGia AS [DonGia], ");
            query.Append("TriGia AS [TriGia], ");
            query.Append("NguyenTe AS [NguyenTe_ID], ");
            query.Append("HS_code AS [MaHS], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDHANGPKHD] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy and P_Code=@Ma and so_pk=@SoPK and ma_pk=@MaPK");

            SqlParameter[] parameters = new SqlParameter[7];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this._MaHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = this._MaDoanhNghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = this._NgayKy;

            parameters[4] = new SqlParameter("@Ma", SqlDbType.VarChar);
            parameters[4].Value = this._P_code;

            parameters[5] = new SqlParameter("@MaPK", SqlDbType.VarChar,4);
            parameters[5].Value = this._MaPhuKien;

            parameters[6] = new SqlParameter("@SoPK", SqlDbType.VarChar);
            parameters[6].Value = this._SoPhuKien;

            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }

    
        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction, PhuKienInfoCollection collection)
        {
            PhuKien pk = new PhuKien();
            foreach (PhuKienInfo itemInfo in collection)
            {
                pk.Load(itemInfo);
                pk.InsertTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDHANGPKHD ( ");
            query.Append("So_HD , ");
            query.Append("Ma_HQHD , ");
            query.Append("DVGC , ");
            query.Append("Ngay_Ky , ");
            query.Append("So_PK , ");
            query.Append("Ma_PK , ");
            query.Append("P_Code , ");
            query.Append("Old_Info , ");
            query.Append("Ten_SP , ");
            query.Append("So_Luong , ");
            query.Append("Ma_DVT , ");


            query.Append("Ma_SP , ");
            query.Append("Xuat_xu , ");
            query.Append("TinhTrang , ");
            query.Append("DonGia , ");
            query.Append("TriGia , ");
            query.Append("NGTe , ");
            query.Append("Ngay_VM , ");
            query.Append("HS_Code , ");
            query.Append("STTHang ) ");

            query.Append("VALUES ( ");

            query.Append("@So_HD , ");
            query.Append("@Ma_HQHD , ");
            query.Append("@DVGC , ");
            query.Append("@Ngay_Ky , ");
            query.Append("@So_PK , ");
            query.Append("@Ma_PK , ");
            query.Append("@P_Code , ");
            query.Append("@Old_Info , ");
            query.Append("@Ten_SP , ");
            query.Append("@So_Luong , ");
            query.Append("@Ma_DVT , ");


            query.Append("@Ma_SP , ");
            query.Append("@Xuat_xu , ");
            query.Append("@TinhTrang , ");
            query.Append("@DonGia , ");
            query.Append("@TriGia , ");
            query.Append("@NGTe , ");
            query.Append("@Ngay_VM , ");
            query.Append("@HS_Code , ");
            query.Append("@STTHang ) ");

            SqlParameter[] parameters = new SqlParameter[20];

            parameters[0] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@Ma_HQHD", SqlDbType.Char,6);
            parameters[1].Value = this._MaHaiQuan;

            parameters[2] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[2].Value = this._MaDoanhNghiep;

            parameters[3] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[3].Value = this._NgayKy;

            parameters[4] = new SqlParameter("@So_PK", SqlDbType.VarChar);
            parameters[4].Value = this._SoPhuKien;

            parameters[5] = new SqlParameter("@Ma_PK", SqlDbType.VarChar,4);
            parameters[5].Value = this._MaPhuKien;

            parameters[6] = new SqlParameter("@P_Code", SqlDbType.VarChar,30);
            parameters[6].Value = this._P_code;

            parameters[7] = new SqlParameter("@Old_Info", SqlDbType.VarChar);
            parameters[7].Value = this._ThongTinCu;

            parameters[8] = new SqlParameter("@Ten_SP", SqlDbType.NVarChar);
            parameters[8].Value = this._tenSp;

            parameters[9] = new SqlParameter("@So_Luong", SqlDbType.Decimal);
            parameters[9].Value = this._SoLuong;

            parameters[10] = new SqlParameter("@Ma_DVT", SqlDbType.Char,3);
            parameters[10].Value = this._MaDVT;



            parameters[11] = new SqlParameter("@Ma_SP", SqlDbType.VarChar,30);
            parameters[11].Value = this.MaNhomSanPham;

            parameters[12] = new SqlParameter("@Xuat_xu", SqlDbType.Char,3);
            parameters[12].Value = this._NuocXX_ID;

            parameters[13] = new SqlParameter("@TinhTrang", SqlDbType.NVarChar);
            parameters[13].Value = this._tinhtrang;

            parameters[14] = new SqlParameter("@DonGia", SqlDbType.Money);
            parameters[14].Value = this._dongia;

            parameters[15] = new SqlParameter("@TriGia", SqlDbType.Money);
            parameters[15].Value = this._trigia;

            parameters[16] = new SqlParameter("@NGTe", SqlDbType.Char,3);
            parameters[16].Value = this._nguyente;

            parameters[17] = new SqlParameter("@Ngay_VM", SqlDbType.DateTime);
            parameters[17].Value = DateTime.Today;

            parameters[18] = new SqlParameter("@HS_Code", SqlDbType.VarChar);
            parameters[18].Value = this._MaHS;

            parameters[19] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[19].Value = this.STTHang;
            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }
        public PhuKienInfoCollection selectHangOfLoaiPhuKien()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("So_PK AS [SoPK], ");
            query.Append("Ma_PK AS [MaPK], ");
            query.Append("P_Code AS [PCode], ");
            query.Append("Old_info AS [ThongTinCu], ");
            query.Append("Ten_SP AS [Ten], ");
            query.Append("So_Luong AS [SoLuong], ");
            query.Append("Ma_DVT AS [DVT_ID], ");
            query.Append("Ma_SP AS [Ma], ");
            query.Append("xuat_xu AS [NuocXX_ID], ");
            query.Append("TinhTrang AS [TinhTrang], ");
            query.Append("DonGia AS [DonGia], ");
            query.Append("TriGia AS [TriGia], ");
            query.Append("NGTe AS [NguyenTe_ID], ");
            query.Append("HS_code AS [MaHS], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDHANGPKHD] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy  and so_pk=@SoPK and ma_pk=@MaPK");

            SqlParameter[] parameters = new SqlParameter[6];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this._MaHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = this._MaDoanhNghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = this._NgayKy;

            parameters[4] = new SqlParameter("@MaPK", SqlDbType.VarChar,4);
            parameters[4].Value = this._MaPhuKien;
            parameters[5] = new SqlParameter("@SoPK", SqlDbType.VarChar);
            parameters[5].Value = this._SoPhuKien;
            IDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
            PhuKienInfoCollection collection = new PhuKienInfoCollection();
            while (reader.Read())
            {
                PhuKienInfo pkInfo = new PhuKienInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) pkInfo.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) pkInfo.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) pkInfo.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) pkInfo.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoPK"))) pkInfo.SoPhuKien = reader.GetString(reader.GetOrdinal("SoPK"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaPK"))) pkInfo.MaPhuKien = reader.GetString(reader.GetOrdinal("MaPK"));
                if (!reader.IsDBNull(reader.GetOrdinal("PCode"))) pkInfo.P_Code = reader.GetString(reader.GetOrdinal("PCode"));
                if (!reader.IsDBNull(reader.GetOrdinal("ThongTinCu"))) pkInfo.ThongTinCu = reader.GetString(reader.GetOrdinal("ThongTinCu"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten"))) pkInfo.Ten = reader.GetString(reader.GetOrdinal("Ten"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) pkInfo.SoLuong = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("DVT_ID"))) pkInfo.DVT_ID = reader.GetString(reader.GetOrdinal("DVT_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma"))) pkInfo.MaNhomSanPham = reader.GetString(reader.GetOrdinal("Ma"));
                if (!reader.IsDBNull(reader.GetOrdinal("NuocXX_ID"))) pkInfo.NuocXX_ID = reader.GetString(reader.GetOrdinal("NuocXX_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("TinhTrang"))) pkInfo.TinhTrang = reader.GetString(reader.GetOrdinal("TinhTrang"));
                if (!reader.IsDBNull(reader.GetOrdinal("DonGia"))) pkInfo.DonGia = reader.GetDecimal(reader.GetOrdinal("DonGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGia"))) pkInfo.TriGia = reader.GetDecimal(reader.GetOrdinal("TriGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("NguyenTe_ID"))) pkInfo.NguyenTe_ID = reader.GetString(reader.GetOrdinal("NguyenTe_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) pkInfo.MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) pkInfo.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
          
                collection.Add(pkInfo);


            }
            reader.Close();
            return collection;
        }
        public void Delete(PhuKienDangKyInfo pk, SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDHANGPKHD WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and So_PK='{4}'", pk.SoHopDong, pk.MaHaiQuan, pk.MaDoanhNghiep, pk.NgayKy, pk.SoPhuKien);
            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
     
        }
    }
}
